﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;

namespace AlphaSolutionsGP
{
    public partial class frmWoodlandsSystemMain : Form
    {
        DataView view = new DataView();

        #region general form functions
        //array lists of records for each class initialised here
        ArrayList diaryEvents = new ArrayList();

        public frmWoodlandsSystemMain()
        {
            InitializeComponent();

        }

        //main form load
        private void frmWoodlandsSystemMain_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'alpha_dbStudentDataSet.STUDENT_GRADE' table. You can move, or remove it, as needed.
            eventTabLoad();
            
        }

        //on exit, check if user is sure they wish to quit
        private void frmWoodlandsSystemMain_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (e.CloseReason == CloseReason.UserClosing)
            {
                if (MessageBox.Show(this, "Are you sure you wish to logout?\nAll unsaved work will be lost.", "Logging out...",
                     MessageBoxButtons.YesNo, MessageBoxIcon.Question)
                    == DialogResult.No) e.Cancel = true;
                else
                {
                    frmLogin f = new frmLogin();
                    f.Show();
                }
            }
        }

        //menu bar exit button
        private void logoutToolStripMenuItem_Click_1(object sender, EventArgs e)
        {
            this.Close();
        }

        private void tabUserControl_Selected(object sender, TabControlEventArgs e)
        {
            lblCurrentTab.Text = tabUserControl.SelectedTab.Text;

            switch (tabUserControl.SelectedTab.Text)
            {
                case "Diary":
                    //here is where a method fetches the latest events
                    eventTabLoad();
                    break;
                case "Student":
                    //method that is run when student tab loads
                    studentTabLoad();
                    break;
                case "Staff":
                    //method that is run when student tab loads
                    staffTabLoad();
                    break;
                case "Course":
                    courseTabLoad();
                    break;
                case "Module":
                    moduleTabLoad();
                    break;
                case "Personal Tutor":
                    personlTutorialTabLoad();
                    break;
                case "Grade":
                    gradeTabLoad();
                    break;
                default:
                    //default tab
                    break;
            }
        }
       

        #endregion

        #region diary events

        private void eventTabLoad()
        {
            // TODO: This line of code loads data into the 'alpha_dbStudentDataSet.STUDENT' table. You can move, or remove it, as needed.
            try
            {
                this.eVENTTableAdapter.Fill(this.alpha_dbStudentDataSet.EVENT);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Connection failed.\n\n" + ex.ToString());
                tabUserControl.SelectedTab = tabDiary;
            }
        }

        /*
         * Diary events button to refresh data grid
         */
        private void btnAgenda_Click(object sender, EventArgs e)
        {
            try
            {
                this.eVENTTableAdapter.Fill(this.alpha_dbStudentDataSet.EVENT);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Connection failed.\n\n" + ex.ToString());
                tabUserControl.SelectedTab = tabDiary;
            }
        }  

        /*
         * cell click listener for the diary data grid - actions happen when 
         * view or delete cell are clicked on particular row
         */
        private void eVENTDataGridView_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (e.ColumnIndex > 1) return;
                //int taskID = (int)dataGridView1[0, e.RowIndex].Value;
                int evId = (int)eVENTDataGridView[2, e.RowIndex].Value;
                string evName = (string)eVENTDataGridView[3, e.RowIndex].Value;
                string evDesc = (string)eVENTDataGridView[4, e.RowIndex].Value;
                DateTime evDate = (DateTime)eVENTDataGridView[5, e.RowIndex].Value;
                TimeSpan evTime = (TimeSpan)eVENTDataGridView[6, e.RowIndex].Value;
                string evPriority = (string)eVENTDataGridView[7, e.RowIndex].Value;
                string studentId = (string)eVENTDataGridView[8, e.RowIndex].Value;
                string staffId = (string)eVENTDataGridView[9, e.RowIndex].Value;
                Event theEvent = new Event(evId, evName, evDesc, evDate.ToString(), evTime.ToString(), evPriority, staffId, studentId);

                if (e.ColumnIndex == 0)
                {
                    //view
                    frmEventView f = new frmEventView(theEvent);                    
                    f.Show();
                }
                else if (e.ColumnIndex == 1)
                {
                    //delete
                    if (MessageBox.Show(this, "Are you sure you wish to delete this event", "Deleting event...",
                        MessageBoxButtons.YesNo, MessageBoxIcon.Question)
                        == DialogResult.Yes)
                    {
                        theEvent.removeEvent();
                        this.eVENTTableAdapter.Fill(this.alpha_dbStudentDataSet.EVENT);
                    }
                }

            }
            catch (Exception ex)
            {
                //do nothing
            }

        }

        //button to search
        private void btnDiaryEventsGeneralSearch_Click(object sender, EventArgs e)
        {
            alpha_dbStudentDataSet.EVENT.Clear();            
            GeneralSearch(txtDiaryEventsGeneralSearch.Text, "EVENT");
        }        
        
        //button to clear search bar and reset data grid
        private void btnDiaryEventsGeneralClear_Click(object sender, EventArgs e)
        {
            eventTabLoad(); //reload the table event
            txtDiaryEventsGeneralSearch.Text = ""; //clear the textbox content ready to be filled in
        }

        //create new instance of event form with blank constructor
        private void bntCreateNew_Click(object sender, EventArgs e)
        {
            frmEventView eventForm = new frmEventView();
            eventForm.Show();
        }

        #endregion

        #region student tab

        //update student list
        private void studentTabLoad()
        {
            // TODO: This line of code loads data into the 'alpha_dbStudentDataSet.STUDENT' table. You can move, or remove it, as needed.
            try
            {
                this.sTUDENTTableAdapter.Fill(this.alpha_dbStudentDataSet.STUDENT);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Connection failed.\n\n" + ex.ToString());
                tabUserControl.SelectedTab = tabDiary;
            }

        }        

        //create new instance of student form with blank constructor
        private void btnAddStudent_Click(object sender, EventArgs e)
        {
            frmStudentProfile f = new frmStudentProfile();
            f.Show();
        }

        /*
         * cell click listener for the student data grid
         * actions happen when view and delete cells are clicked
         */
        private void sTUDENTDataGridView_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                //MessageBox.Show(e.ColumnIndex.ToString());
                if (e.ColumnIndex > 1) return;
                int stId = int.Parse(sTUDENTDataGridView[2, e.RowIndex].Value.ToString());
                //int stId = 20100131;
                string stCrsCode = sTUDENTDataGridView[3, e.RowIndex].Value.ToString();
                int stLevel = int.Parse(sTUDENTDataGridView[4, e.RowIndex].Value.ToString());
                string fName = sTUDENTDataGridView[5, e.RowIndex].Value.ToString();
                string mName = sTUDENTDataGridView[6, e.RowIndex].Value.ToString();
                string lName = sTUDENTDataGridView[7, e.RowIndex].Value.ToString();
                string dob = sTUDENTDataGridView[8, e.RowIndex].Value.ToString();
                string nationality = sTUDENTDataGridView[9, e.RowIndex].Value.ToString();
                string homeTwn = sTUDENTDataGridView[10, e.RowIndex].Value.ToString();
                string streetAdd = sTUDENTDataGridView[11, e.RowIndex].Value.ToString();
                string postcode = sTUDENTDataGridView[12, e.RowIndex].Value.ToString();
                string city = sTUDENTDataGridView[13, e.RowIndex].Value.ToString();
                string mobile = sTUDENTDataGridView[14, e.RowIndex].Value.ToString();
                string tel = sTUDENTDataGridView[15, e.RowIndex].Value.ToString();
                string dtEnrolled = sTUDENTDataGridView[16, e.RowIndex].Value.ToString();
                string dtGrad = sTUDENTDataGridView[17, e.RowIndex].Value.ToString();
                string status = sTUDENTDataGridView[18, e.RowIndex].Value.ToString();
                string pEmail = sTUDENTDataGridView[19, e.RowIndex].Value.ToString();
                string aEmail = sTUDENTDataGridView[20, e.RowIndex].Value.ToString();
                string password = sTUDENTDataGridView[21, e.RowIndex].Value.ToString();
                string imgLoc = sTUDENTDataGridView[22, e.RowIndex].Value.ToString();


                //create a student object
                //that's a lot of variables to pass
                Student theStudent = new Student(stId, dtEnrolled, stLevel, dtGrad, password, imgLoc, stCrsCode, nationality, homeTwn, status, fName, mName, lName, tel, pEmail, aEmail, postcode, streetAdd, city, mobile, dob);

                //view button
                if (e.ColumnIndex == 0)
                {
                    //show the form
                    frmStudentProfile f = new frmStudentProfile(theStudent);
                    f.Show();
                }
                else if (e.ColumnIndex == 1)
                {
                    //delete
                    if (MessageBox.Show(this, "Are you sure you wish to delete this student", "Deleting student...",
                        MessageBoxButtons.YesNo, MessageBoxIcon.Question)
                        == DialogResult.Yes)
                    {
                        //student . remove
                        theStudent.deleteStudent();

                    }
                }
            }
            catch (Exception ex)
            {
                //do nothgin
            }
        }

        //refresh the data grid
        private void btnViewAllStudent_Click(object sender, EventArgs e)
        {
            studentTabLoad();
            txtStudentGeneralSearch.Clear(); //clear the text into the student id text box
        }

        //button general search
        private void btnStudentGeneralSearch_Click(object sender, EventArgs e)
        {
            //studentGeneralSearch(txtStudentGeneralSearch.Text);
            alpha_dbStudentDataSet.STUDENT.Clear();
            GeneralSearch(txtStudentGeneralSearch.Text, "STUDENT");
        }

        //clear the search bar and reset data grid
        private void btnStudentGeneralClear_Click(object sender, EventArgs e)
        {
            studentTabLoad();
            txtStudentGeneralSearch.Text = "";
        }

        #endregion

        #region module tab
        //create new instance of module form with blank constructor
        private void btnAddModule_Click(object sender, EventArgs e)
        {
            frmModuleView f = new frmModuleView();
            f.Show();
        }
        //load / reset data grid
        private void moduleTabLoad()
        {
            try
            {
                this.mODULETableAdapter.Fill(this.alpha_dbStudentDataSet.MODULE);                
            }
            catch (System.Data.ConstraintException ex)
            {
                //MessageBox.Show(ex.ToString());
            }

        }
        private void btnViewAllModules_Click(object sender, EventArgs e)
        {
            moduleTabLoad();
        }
        /*
         * cell click listener for the module data grid
         * actions perform when view and delete buttons clicked
         */
        private void mODULEDataGridView_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (e.ColumnIndex > 1) return;
                //set up module object for currently selected module
                string mCode = mODULEDataGridView[2, e.RowIndex].Value.ToString();
                string cCode = mODULEDataGridView[3, e.RowIndex].Value.ToString();
                string title = mODULEDataGridView[4, e.RowIndex].Value.ToString();
                string field = mODULEDataGridView[5, e.RowIndex].Value.ToString();
                //int level = int.Parse(mODULEDataGridView[6, e.RowIndex].Value.ToString());
                string slevel = mODULEDataGridView[6, e.RowIndex].Value.ToString();
                int level = int.Parse(slevel);
                string description = mODULEDataGridView[7, e.RowIndex].Value.ToString();
                int credits = int.Parse(mODULEDataGridView[8, e.RowIndex].Value.ToString());
                string staff = mODULEDataGridView[9, e.RowIndex].Value.ToString();

                Module theModule = new Module(mCode, cCode, title, field, description, credits, staff, level);

                if (e.ColumnIndex == 0)
                {
                    frmModuleView m = new frmModuleView(theModule);
                    m.Show();
                }
                else
                {
                    if (e.ColumnIndex == 1)
                    {
                        //delete
                        if (MessageBox.Show(this, "Are you sure you wish to delete this module", "Deleting module...",
                            MessageBoxButtons.YesNo, MessageBoxIcon.Question)
                            == DialogResult.Yes)
                        {
                            theModule.deleteModule();
                            moduleTabLoad();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //do nothing
            }
        }
        //button to search for module
        private void btnModuleFilter_Click(object sender, EventArgs e)
        {
            alpha_dbStudentDataSet.MODULE.Clear();
            GeneralSearch(txtModuleGeneralSearch.Text, "MODULE");
        }
        //reset search bar and data grid
        private void btnModuleGeneralSearchClear_Click(object sender, EventArgs e)
        {
            moduleTabLoad();
            txtModuleGeneralSearch.Text = "";
        }

        #endregion

        #region personal tutor tab

        //update data grid
        private void personlTutorialTabLoad()
        {
            try
            {
                this.eVENT_VIEWTableAdapter.Fill(this.alpha_dbStudentDataSet.EVENT_VIEW);
            }
            catch (System.Data.ConstraintException ex)
            {
                //MessageBox.Show(ex.ToString());
            }
        }
        /*
         * cell click listener for personal tutor data grid
         */

        private void eVENT_VIEWDataGridView_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (e.ColumnIndex > 1) return;
                //int taskID = (int)dataGridView1[0, e.RowIndex].Value;
                int evId = (int)eVENT_VIEWDataGridView[2, e.RowIndex].Value;
                string evName = (string)eVENT_VIEWDataGridView[3, e.RowIndex].Value;
                string evDesc = (string)eVENT_VIEWDataGridView[4, e.RowIndex].Value;
                DateTime evDate = (DateTime)eVENT_VIEWDataGridView[5, e.RowIndex].Value;
                TimeSpan evTime = (TimeSpan)eVENT_VIEWDataGridView[6, e.RowIndex].Value;
                string evPriority = (string)eVENT_VIEWDataGridView[7, e.RowIndex].Value;
                string studentId = (string)eVENT_VIEWDataGridView[8, e.RowIndex].Value;
                string staffId = (string)eVENT_VIEWDataGridView[9, e.RowIndex].Value;
                Event theEvent = new Event(evId, evName, evDesc, evDate.ToString(), evTime.ToString(), evPriority, staffId, studentId);

                if (e.ColumnIndex == 0)
                {
                    //view
                    frmEventView f = new frmEventView(theEvent);
                    f.Show();
                }
                else if (e.ColumnIndex == 1)
                {
                    //delete
                    if (MessageBox.Show(this, "Are you sure you wish to delete this personal tutorial", "Deleting personal tutorial...",
                        MessageBoxButtons.YesNo, MessageBoxIcon.Question)
                        == DialogResult.Yes)
                    {
                        theEvent.removeEvent();
                        this.eVENT_VIEWTableAdapter.Fill(this.alpha_dbStudentDataSet.EVENT_VIEW);
                    }
                }

            }
            catch (Exception ex)
            {
                //do nothing
            }
        }
        
        //refresh data grid
        private void btnPersonalTutorialViewAll(object sender, EventArgs e)
        {
            //initialStart();
            personlTutorialTabLoad();
        }
        // proceed to open event view form with no details passed
        private void btnAddPersonalTutorial_Click(object sender, EventArgs e)
        {
            frmEventView f = new frmEventView(1);
            f.Show();
        }
        //search button for PT tab
        private void btnPersonalTutorFilter_Click(object sender, EventArgs e)
        {
            alpha_dbStudentDataSet.EVENT_VIEW.Clear();
            GeneralSearch(txtPersonalTutorGeneralSearch.Text, "EVENT_VIEW");
        }
        //reset PT tab search
        private void btnPersonalTutorGeneralSearchClear_Click(object sender, EventArgs e)
        {
            personlTutorialTabLoad();
            txtPersonalTutorGeneralSearch.Text = "";
        }

        #endregion

        #region staff tab
        //inital load of staff data grid
        private void staffTabLoad()
        {
            // TODO: This line of code loads data into the 'alpha_dbStudentDataSet.STUDENT' table. You can move, or remove it, as needed.
            try
            {
                txtStaffGeneralSearch.Text = "";
                this.sTAFFTableAdapter.Fill(this.alpha_dbStudentDataSet.STAFF);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Connection failed.\n\n" + ex.ToString());
                tabUserControl.SelectedTab = tabDiary;
            }

        }
        /*
         * cell click listener for staff data grid where
         * actions are fired when view and delete buttons clicked
         */
        private void sTAFFDataGridView_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (e.ColumnIndex > 1) return;

                string staffId = sTAFFDataGridView[2, e.RowIndex].Value.ToString();
                string firstname = sTAFFDataGridView[3, e.RowIndex].Value.ToString();
                string middleName = sTAFFDataGridView[4, e.RowIndex].Value.ToString();
                string surname = sTAFFDataGridView[5, e.RowIndex].Value.ToString();
                string DOB = sTAFFDataGridView[6, e.RowIndex].Value.ToString();
                string nationality = sTAFFDataGridView[7, e.RowIndex].Value.ToString();
                string homeTown = sTAFFDataGridView[8, e.RowIndex].Value.ToString();
                string streetAddress = sTAFFDataGridView[9, e.RowIndex].Value.ToString();
                string postcode = sTAFFDataGridView[10, e.RowIndex].Value.ToString();
                string city = sTAFFDataGridView[11, e.RowIndex].Value.ToString();
                string mobileNumber = sTAFFDataGridView[12, e.RowIndex].Value.ToString();
                string telephoneNumber = sTAFFDataGridView[13, e.RowIndex].Value.ToString();
                string emailAddress = sTAFFDataGridView[14, e.RowIndex].Value.ToString();
                string role = sTAFFDataGridView[15, e.RowIndex].Value.ToString();
                string dtEmployemntFrom = sTAFFDataGridView[16, e.RowIndex].Value.ToString();
                string dtEmployemntTo = sTAFFDataGridView[17, e.RowIndex].Value.ToString();
                string status = sTAFFDataGridView[18, e.RowIndex].Value.ToString();
                string reasonDormancy = sTAFFDataGridView[19, e.RowIndex].Value.ToString();
                string password = sTAFFDataGridView[20, e.RowIndex].Value.ToString();

                //create a staff object
                Staff theStaff = new Staff(staffId, firstname, middleName, surname, DOB, nationality, homeTown, streetAddress,
                    postcode, city, mobileNumber, telephoneNumber, emailAddress, role, dtEmployemntFrom, dtEmployemntTo, status, reasonDormancy, password);

                //view button
                if (e.ColumnIndex == 0)
                {
                    //show the form
                    frmStaffProfile form = new frmStaffProfile(theStaff);
                    form.Show();
                }
                else if (e.ColumnIndex == 1)
                {
                    //delete
                    if (MessageBox.Show(this, "Are you sure you wish to delete this Staff record", "Deleting event...",
                        MessageBoxButtons.YesNo, MessageBoxIcon.Question)
                        == DialogResult.Yes)
                    {
                        //staff . remove
                        theStaff.deleteStaff();

                    }
                }
            }
            catch (Exception ex)
            {
                // do nothing
            }
        }
        //refresh staff data grid
        private void btnViewAllStaff_Click(object sender, EventArgs e)
        {
            staffTabLoad();
            txtStaffGeneralSearch.Clear(); //clear the text into the staff id text box
        }
        //create new instance of staff profile form with blank constructor
        private void btnAddNewStaff_Click(object sender, EventArgs e)
        {
            frmStaffProfile staffForm = new frmStaffProfile();
            staffForm.Show();
        }
        //reset search bar and data grid
        private void btnClearStaffSearch_Click(object sender, EventArgs e)
        {
            txtStaffGeneralSearch.Text = " "; // set the text box to blank value
            staffTabLoad(); //reload the tab
        }
        //search button for staff data grid
        private void btnStaffFilter_Click(object sender, EventArgs e)
        {
            //searchStaffTab(txtStaffID.Text);
            alpha_dbStudentDataSet.STAFF.Clear();
            GeneralSearch(txtStaffGeneralSearch.Text, "STAFF");
        }
        #endregion

        #region course tab
        //initial start of the course tab load
        private void courseTabLoad()
        {
            // TODO: This line of code loads data into the 'alpha_dbStudentDataSet.STUDENT' table. You can move, or remove it, as needed.
            try
            {
                this.cOURSETableAdapter.Fill(this.alpha_dbStudentDataSet.COURSE);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Connection failed.\n\n" + ex.ToString());
                tabUserControl.SelectedTab = tabDiary;
            }
        }
        //create new instance of the course form using blank constructor
        private void btnAddNewCourse_Click(object sender, EventArgs e)
        {
            frmCourseView f = new frmCourseView();
            f.Show();
        }
        //refresh the course tab data grid
        private void btnViewAllCourses_Click(object sender, EventArgs e)
        {
            courseTabLoad();
        }
        /*
         * cell click listener for the course data grid
         * view and delete actions are performed within this
         */
        private void cOURSEDataGridView_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (e.ColumnIndex > 1)
                    return;
                string cCode = cOURSEDataGridView[2, e.RowIndex].Value.ToString();
                string cTitle = cOURSEDataGridView[3, e.RowIndex].Value.ToString();
                int cYr = int.Parse(cOURSEDataGridView[4, e.RowIndex].Value.ToString());
                string cDpt = cOURSEDataGridView[5, e.RowIndex].Value.ToString();
                int cMax = int.Parse(cOURSEDataGridView[6, e.RowIndex].Value.ToString());
                string cDesc = cOURSEDataGridView[7, e.RowIndex].Value.ToString();
                string cReq = cOURSEDataGridView[8, e.RowIndex].Value.ToString();
                //make an instance of the the selected Course
                Course theCourse = new Course(cCode, cTitle, cDpt, cYr, cMax, cDesc, cReq);
                if (e.ColumnIndex == 0)
                {
                    frmCourseView f = new frmCourseView(theCourse);
                    f.Show();
                }
                else if (e.ColumnIndex == 1)
                {
                    //delete
                    if (MessageBox.Show(this, "Are you sure you wish to delete this Course?", "Deleting course...",
                         MessageBoxButtons.YesNo, MessageBoxIcon.Question)
                        == DialogResult.Yes)
                    {
                        //student . remove                    
                        theCourse.deleteCourse();
                        this.cOURSETableAdapter.Fill(this.alpha_dbStudentDataSet.COURSE);

                    }
                }
            }
            catch (Exception ex)
            {
                //do nothing
            }

        }
        //search button for course tab
        private void btnCourseFilter_Click(object sender, EventArgs e)
        {
            alpha_dbStudentDataSet.COURSE.Clear();
            GeneralSearch(txtCourseGeneralSearch.Text, "COURSE");
        }
        //reset search on course tab
        private void btnClearCourseSearch_Click(object sender, EventArgs e)
        {
            courseTabLoad();
            txtCourseGeneralSearch.Text = "";
        }

        #endregion

        #region grade tab
        void gradeTabLoad()
        {
            try
            {
                this.sTUDENT_GRADETableAdapter.Fill(this.alpha_dbStudentDataSet.STUDENT_GRADE);
            }
            catch (System.Data.ConstraintException ex)
            {
                //MessageBox.Show(ex.ToString());
            }
        }

        private void btnGradeFilter_Click(object sender, EventArgs e)
        {
            alpha_dbStudentDataSet.STUDENT_GRADE.Clear();
            GeneralSearch(txtGradeSearch.Text, "STUDENT_GRADE");
        }

        private void btnGradeClear_Click(object sender, EventArgs e)
        {
            gradeTabLoad();
            txtGradeSearch.Text = "";
        }

        #endregion

        #region useful functions
        //DINAMICALLY FILL IN A DROPDOWN MENU
        private void fillInCombo(ComboBox cb, string column, string table)
        {
            MySqlConnection connection = new MySqlConnection("server=194.81.104.22;" + "User Id=alpha;" + "database=alpha_db;" + "password=bravo;");
            try
            {                
                connection.Open();
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT DISTINCT " + column + " FROM " + table + ""; //select the required column from table
                MySqlDataReader myReader = cmd.ExecuteReader(); //execute the reader
                while (myReader.Read())
                {
                    column = myReader.GetString(column); //save the column value
                    cb.Items.Add(column); // add the value to the combo box
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Unable to fill table due to the following error:\n\n" + ex.ToString()); //failure message
            }

            finally
            {
                connection.Close(); //close the connection

            }
        }

        //dinamically search content within a tab
        private void GeneralSearch(string word, string table)
        {
            Cursor.Current = Cursors.WaitCursor;            
            int CHOICE = 0;

            if (table.Equals("EVENT"))
            {
                CHOICE = 1;
            }
            if (table.Equals("STUDENT"))
            {
                CHOICE = 2;
            }
            if (table.Equals("STAFF"))
            {
                CHOICE = 3;
            }
            if (table.Equals("COURSE"))
            {
                CHOICE = 4;
            }
            if (table.Equals("MODULE"))
            {
                CHOICE = 5;
            }
            if (table.Equals("EVENT_VIEW"))
            {
                CHOICE = 6;
            }
            if (table.Equals("STUDENT_GRADE"))
            {
                CHOICE = 7;
            }

            MySqlConnection connection = new MySqlConnection("server=194.81.104.22;" + "User Id=alpha;" + "database=alpha_db;" + "password=bravo;");
            try
            {
                connection.Open();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show("Connection failed");
            }
            try
            {
                bool found = false;
                MySqlCommand cmd = connection.CreateCommand();
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);                
                cmd.CommandText = "SELECT * FROM " + table + "";
                MySqlDataReader myReader = cmd.ExecuteReader(); //execute the reader

                int columnCount = 0; //initialize the column count to zero
                String[] columns = new String[myReader.FieldCount];
                myReader.Read();

                while (columnCount < columns.Length)
                {
                    columns[columnCount] = myReader.GetName(columnCount); //store each column name                    
                    columnCount++;
                }
                myReader.Close();

                

                for (int i = 0; i < columns.Length; i++)
                {
                    cmd.CommandText = "SELECT COUNT(*) FROM " + table + " WHERE " + columns[i] + " LIKE '%" + word + "%' "; //count how many rows are returned bu the query
                    cmd.CommandType = CommandType.Text; //make the returned value an integer
                    int RowCount = Convert.ToInt32(cmd.ExecuteScalar());
                    if (RowCount > 0)
                    {
                        cmd.CommandText = "SELECT * FROM " + table + " WHERE " + columns[i] + " LIKE'%" + word + "%'"; //query the table by filtering with the given staff id    
                        
                        switch(CHOICE)
                        {
                            case 1: da.Fill(this.alpha_dbStudentDataSet.EVENT);
                                break;
                            case 2: da.Fill(this.alpha_dbStudentDataSet.STUDENT);
                                break;
                            case 3: da.Fill(this.alpha_dbStudentDataSet.STAFF);
                                break;
                            case 4: da.Fill(this.alpha_dbStudentDataSet.COURSE);
                                break;
                            case 5: da.Fill(this.alpha_dbStudentDataSet.MODULE);
                                break;
                            case 6: da.Fill(this.alpha_dbStudentDataSet.EVENT_VIEW);
                                break;
                            case 7: da.Fill(this.alpha_dbStudentDataSet.STUDENT_GRADE);
                                break;
                            default: Console.WriteLine();
                                break;
                        }
                        
                        found = true;
                    }
                }

                //if no results are found
                if (!found)
                {
                    MessageBox.Show("No results found!"); //show message

                    switch (CHOICE)
                    {
                        case 1: eventTabLoad(); //reload the tab
                                txtDiaryEventsGeneralSearch.Text = ""; // clear the search text box
                            break;
                        case 2: studentTabLoad(); //reload the tab
                                txtStudentGeneralSearch.Text = ""; // clear the search text box
                            break;
                        case 3: staffTabLoad(); //reload the tab
                                txtStaffGeneralSearch.Text = ""; // clear the search text box
                            break;
                        case 4: courseTabLoad(); //reload the tab
                                txtCourseGeneralSearch.Text = ""; // clear the search text box
                            break;
                        case 5: moduleTabLoad(); //reload the tab
                                txtModuleGeneralSearch.Text = ""; // clear the search text box
                            break;
                        case 6: personlTutorialTabLoad(); //reload the tab
                                txtPersonalTutorGeneralSearch.Text = ""; // clear the search text box
                            break;
                        case 7: gradeTabLoad(); //reload the tab
                                txtGradeSearch.Text = ""; // clear the search text box
                            break;
                        default: Console.WriteLine();
                            break;
                    }
                }
                
                da.Dispose();


            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Unable to fill due to the following error:\n\n" + ex.ToString());
            }
            finally
            {
                connection.Close();

            }
            Cursor.Current = Cursors.Arrow;

        }

        #endregion

        private void splitHeadBody_Panel1_Paint(object sender, PaintEventArgs e)
        {

        }


    }
}
